package eRepublik;

import java.sql.*;
import java.text.*;
import java.util.*;
import java.util.Date;

import main.*;

public class ultramarines implements Runnable {
	
	//Declare variables
	private Citizen cit = new Citizen();
	private MyBot bot = new MyBot();
	private String sender;
	private String channel;
	private String message;
	
	//Errors
	String[] error ={	"4Error: Only QM's and Admin's can use this command!", 
						"4Error: There is a fault within the API. Please try again soon.",
						"4Error: Soldier does not exist or cannot be found.",
						"4MySQL Error: Please try again in a moment. If the issue persists please join #eRepBot for help.",
						"4Error: This IRC nick is not linked, please see !help eRepublik for more information.",
						"4Error: Citizen does not exist or has not linked their name."
					};

	//Constructor
	public ultramarines(MyBot myBot, String channel, String sender, String message) {
		this.channel = channel; this.sender = sender; this.message = message;
		this.bot = myBot;
	}
	
	private Connection getmySQLConnection() {
		try {
			//Create a new instance for the connector
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			
			//Set up the connection with the correct information
			Connection con = DriverManager.getConnection("jdbc:mysql://hub.ultramarines.org:3306/um_hub?user=um_hub&password=um_r0xx0r");
			//Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/um_hub?user=root&password=20071982");
			
			//Return the connection
			return con;
		} catch (Exception e) {
			// If there is an error, print the error and return a null value
			System.out.println("Exception: " + e.getMessage());
			return null;
		}
	}
	
	private boolean soldierExists(String id) {
		boolean exists = false;
		Connection con = getmySQLConnection();
		
		try {
			Statement state = con.createStatement();
			//First check current requests
			ResultSet results = state.executeQuery("SELECT * FROM military WHERE CitID = '" + id + "'");
			if (results.next()) {
				exists = true;
			}
			con.close();
		} catch (Exception e) {
			//Print the Exception
			System.out.println("Exception: " + e.getMessage());
		}
		return exists;
	}
	
	private String supply(String id, String name, String type) {
		//Set date for PST
		SimpleDateFormat isoFormat = new SimpleDateFormat("yyyy-MM-dd");
		isoFormat.setTimeZone(TimeZone.getTimeZone("PST"));
		Date date = new Date();
		String date2 = isoFormat.format(date);
		
		//Initialize variables
		String msg = "";
		int rTanks = 0; int rFood = 0; int sTanks = 0; int sFood = 0;
		boolean rExists = false;
		
		Connection con = getmySQLConnection();
		
		try {
			Statement state = con.createStatement();
			//First check current requests
			ResultSet results = state.executeQuery("SELECT tanks,food FROM requests WHERE name = '" + name + "' AND date = '"+date2+"'");
			if (results.next()) {
				rTanks = results.getInt("tanks");
				rFood = results.getInt("food");
				rExists = true;
			}
			//Then check supplies already sent today
			ResultSet results2 = state.executeQuery("SELECT tanks,food FROM supply_history WHERE name = '"+name+"' AND date = '"+date2+"'");
			if (results2.next()) {
				sTanks = results2.getInt("tanks");
				sFood = results2.getInt("food");
			}
			
			//Now add to get total
			int tTanks = rTanks + sTanks;
			int tFood = rFood + sFood;
			
			//Do checks now
			if ((type.equals("tanks") || type.equals("both")) && tTanks >= 21) {
				msg = "4This request will bring you over your daily Tank limit.";
			} else if ((type.equals("food") || type.equals("both")) && tFood >= 1800) {
				msg = "4This request will bring you over your daily Food limit.";
			} else {
				Statement st = con.createStatement();
				int tanks = 0;
				int food = 0;
				if (type.equals("tanks")) {
					tanks = 7;
				} else if (type.equals("food")) {
					food = 600;
				} else if (type.equals("both")) {
					tanks = 7;
					food = 600;
				}
				//If theres already a row...
				if (rExists == true) {
					st.executeUpdate("UPDATE requests SET tanks = tanks + "+tanks+", food = food + "+food+" WHERE name = '"+name+"' AND date = '"+date2+"'");
				} else {
					st.executeUpdate("INSERT INTO requests (id, name, tanks, food, date) VALUES ("+id+", '"+name+"', "+tanks+", "+food+", '"+date2+"')");
				}
				msg = "12Your request has been received. Use @track to keep track of your requests.";
			}
			con.close();
		} catch (Exception e) {
			//Print the Exception
			System.out.println("Exception: " + e.getMessage());
		}
		
		return msg;
	}
	
	private String track(String name) {
		SimpleDateFormat isoFormat = new SimpleDateFormat("yyyy-MM-dd");
		isoFormat.setTimeZone(TimeZone.getTimeZone("PST"));
		Date date = new Date();
		String date2 = isoFormat.format(date);
		
		int rTanks = 0; int rFood = 0; int sTanks = 0; int sFood = 0;
		Connection con = getmySQLConnection();
		
		try {
			Statement state = con.createStatement();
			//First check current requests
			ResultSet results = state.executeQuery("SELECT tanks,food FROM requests WHERE name = '" + name + "' AND date = '"+date2+"'");
			if (results.next()) {
				rTanks = results.getInt("tanks");
				rFood = results.getInt("food");
			}
			//Then check supplies already sent today
			ResultSet results2 = state.executeQuery("SELECT tanks,food FROM supply_history WHERE name = '"+name+"' AND date = '"+date2+"'");
			if (results2.next()) {
				sTanks = results2.getInt("tanks");
				sFood = results2.getInt("food");
			}
		} catch (Exception e) {
			System.out.println("Exception: " + e.getMessage());
		}
		String msg = "2Name: 1"+name+" 3|| 2Supplied:7 "+sTanks+" tanks &&12 "+sFood+" health 3|| 2Pending:7 "+rTanks+" tanks &&12 "+rFood+" health";
		return msg;
	}
	
	private static Date subDays(Date date, int days) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        cal.add(Calendar.DATE, -days); //minus number would decrement the days
        return cal.getTime();
    }
	
	private void history(String name) throws Exception {
		Connection con = getmySQLConnection();
		
		for(int i=0; i < 7; i++) {
			int rTanks = 0; int rFood = 0; int sTanks = 0; int sFood = 0;
			
			//Setup date shiz
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
			Date date = new Date();
			format.setTimeZone(TimeZone.getTimeZone("PST"));
			String sourceDate = format.format(date);
			Date myDate = format.parse(sourceDate);
			
			myDate = subDays(myDate, i);
			String date2 = format.format(myDate);
		
			try {
				Statement state = con.createStatement();
				//First check current requests
				ResultSet results = state.executeQuery("SELECT tanks,food FROM requests WHERE name = '" + name + "' AND date = '"+date2+"'");
				if (results.next()) {
					rTanks = results.getInt("tanks");
					rFood = results.getInt("food");
				}
				//Then check supplies already sent today
				ResultSet results2 = state.executeQuery("SELECT tanks,food FROM supply_history WHERE name = '"+name+"' AND date = '"+date2+"'");
				if (results2.next()) {
					sTanks = results2.getInt("tanks");
					sFood = results2.getInt("food");
				}
				
				String msg = "2Name: 1"+name+" 3|| 2Date:7 "+date2+" 3|| 2Supplied:7 "+sTanks+" tanks &&12 "+sFood+" health 3|| 2Pending:7 "+rTanks+" tanks &&12 "+rFood+" health";
				bot.sendMessage(sender,msg);
			} catch (Exception e) {
				System.out.println("Exception: " + e.getMessage());
			}
		}
	}
	
	private int getRank(String id) {
		int check = 0;
		Connection con = getmySQLConnection();
		
		try {
			Statement state = con.createStatement();
			//First check current requests
			ResultSet results = state.executeQuery("SELECT position FROM military WHERE CitID = '" + id + "'");
			while (results.next() == true) {
				check = results.getInt("Position");
			}
		} catch (Exception e) {
			System.out.println("Exception: " + e.getMessage());
		}
		
		return check;
	}
	
	private void getRequests() {
		String msg = null;
		Connection con = getmySQLConnection();
		
		try {
			Statement state = con.createStatement();
			ResultSet results = state.executeQuery("SELECT id, name, SUM(food) as food, SUM(tanks) as tanks, date FROM requests GROUP BY id");
			while (results.next() == true) {
				msg = "2Name: 1"+results.getString("name")+" 3|| " +
					  "2Pending:7 "+results.getString("tanks")+" tanks &&12 "+results.getString("food")+" health 3|| " +
					  "2Link:1 http://www.erepublik.com/en/economy/donate-items/"+results.getString("id");
				bot.sendMessage(sender,msg);
			}
		} catch (Exception e) {
			System.out.println("Exception: " + e.getMessage());
		}
		
	}
	
	private void fillReq(String name) {
		Connection con = getmySQLConnection();
		String[][] res = new String[3][3];
		String id = null;
		int i = 0;
		try {
			Statement state = con.createStatement();
			//First get the current ones, seperately this time!
			ResultSet results = state.executeQuery("SELECT * FROM requests WHERE name = '"+name+"'");
			while (results.next() == true) {
				res[i][0] = results.getString("date");
				res[i][1] = results.getString("tanks");
				res[i][2] = results.getString("food");
				id = results.getString("id");
				i++;
			}
			for (int j=0;j < 3;j++) {
				if (res[j][0] != null) {
					Statement st = con.createStatement();
					//Get vars
					String tanks = res[j][1];
					String food = res[j][2];
					String date = res[j][0];
					//Then check if one already exists for the date
					ResultSet results2 = state.executeQuery("SELECT * FROM supply_history WHERE name = '"+name+"' AND date = '"+date+"'");
					if (results2.next()) {
						st.executeUpdate("UPDATE supply_history SET tanks = tanks + "+tanks+", food = food + "+food+" WHERE name = '"+name+"' AND date = '"+date+"'");
					} else {
						st.executeUpdate("INSERT INTO supply_history (id, name, tanks, food, date) VALUES ('"+id+"', '"+name+"', '"+tanks+"', '"+food+"', '"+date+"')");
					}
				}
			}
			Statement st2 = con.createStatement();
			st2.executeUpdate("DELETE FROM requests WHERE name = '"+name+"'");
			con.close();
		} catch (Exception e) {
			System.out.println("Exception: " + e.getMessage());
		}
	}

	public void run() {		 
		if (message.toLowerCase().startsWith("@supply")) {
			//Get ID
			String id = bot.checkLink(sender);
			// Do name stuff
			String name = bot.getNameFromID(id);
			//Check soldier exists
			boolean exists = soldierExists(id);
			
			if (message.toLowerCase().equals("@supply")) {
				bot.sendMessage(channel,"4Correct format is @supply TYPE - see @help for more information.");
			} else if (id.equals("Error")) {
				bot.sendMessage(channel,error[4]);
			} else if (exists == false) {
				bot.sendMessage(channel,error[2]);
			} else {
				String type = message.replace("@supply ","");
				String supply = supply(id,name,type);
				
				bot.sendMessage(channel,supply);
			}
		}
		
		if (message.toLowerCase().startsWith("@track")) {
			String id = null;
			String name = null;
			//Get ID
			if (message.equals("@track")) {
				id = bot.checkLink(sender);
			} else {
				name = message.replace("@track ","");
				id = bot.checkLink(name);
				if (id.equals("Error")) {
					id = cit.citizenSearch(name);
				}
			}
			//Check soldier exists
			boolean exists = soldierExists(id);
			
			name = bot.getNameFromID(id);
			
			if (id.equals("Error") && message.equals("@track")) {
				bot.sendMessage(channel,error[4]);
			} else if (id.indexOf("exist") > 0) {
				bot.sendMessage(channel,error[5]);
			} else if (exists == false) {
				bot.sendMessage(channel,error[2]);
			} else {
				String track = track(name);
				bot.sendMessage(channel,track);
			}
		}
		
		if (message.toLowerCase().startsWith("@history")) {
			String id = null;
			String name = null;
			//Get ID
			if (message.equals("@history")) {
				id = bot.checkLink(sender);
			} else {
				name = message.replace("@history ","");
				id = bot.checkLink(name);
				if (id.equals("Error")) {
					id = cit.citizenSearch(name);
				}
			}
			//Check soldier exists
			boolean exists = soldierExists(id);
			
			name = bot.getNameFromID(id);

			if (id.equals("Error") && message.equals("@track")) {
				bot.sendMessage(channel,error[4]);
			} else if (id.indexOf("exist") > 0) {
				bot.sendMessage(channel,error[5]);
			} else if (exists == false) {
				bot.sendMessage(channel,error[2]);
			} else {
				try {
					history(name);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		
		if (message.toLowerCase().startsWith("@requests")) {
			String id = bot.checkLink(sender);
			boolean exists = soldierExists(id);
			 
			if (id.indexOf("Error") > 0) {
				bot.sendMessage(channel,error[4]);
			} else if (exists == false) {
				bot.sendMessage(channel,error[2]);
			} else {
				int isQM = getRank(id);

				if (isQM > 1) {
					getRequests();
				} else {
					bot.sendMessage(channel,error[0]);
				}
			}		
		}
		
		if (message.toLowerCase().startsWith("@fill")) {
			String id = bot.checkLink(sender);
			
			//Ensure a name is entered
			if (message.toLowerCase().equals("@fill")) {
				bot.sendMessage(channel,"4You must enter a name!");
			//Check sender is linked
			} else if (id.indexOf("Error") > 0) {
				bot.sendMessage(channel,error[4]);
			} else {
				
				//Get name
				String name = message.replace("@fill ","");
				String id2 = bot.checkLink(name);
				
				if (id2.equals("Error")) {
					id2 = cit.citizenSearch(name);
				}
				boolean exists = soldierExists(id2);
				
				//If cit doesnt exist
				if (id2.indexOf("exist") > 0) {
					bot.sendMessage(channel,error[5]);
				//If soldier does not exist
				} else if (exists == false) {
					bot.sendMessage(channel,error[2]);
				} else {
					int isQM = getRank(id);
					if (isQM > 1) {
						name = bot.getNameFromID(id2);
						fillReq(name);
						bot.sendMessage(channel,"2All pending requests for 1"+name+" 2have been filled.");
					} else {
						bot.sendMessage(channel,error[0]);
					}
				}	
			}
		}
		
		if (message.equals("@help")) {
			String[] help ={
					"10eRepBot Commands: !help",
					"10@supply TYPE  Logs supply request. Type is either 'tanks', 'food' or 'both'",
					"10@track NAME  Shows current supply status for today",
					"10@history NAME  Shows supply status for the last 7 days",
					"10@requests  Shows list of outstanding requests (Staff only)",
					"10@fill NAME  Marks all oustanding requests for that soldier as filled (Staff only)"
			};
			
			for (String h : help) {
				bot.sendMessage(channel,h);
			}
		}
	}
}
